﻿/******************************************************************
** 文 件 名: Bu_AuditBatch_DAL.cs
** Copyright (c) 2010-2012 Honestysoft (GuangZhou) Co.,LTD
** 创 建 人: 此代码由生成器自动生成
** 创建日期: 2012-3-21 15:53:08
** 描    述: 实现公共部分的数据访问封装，提供资金申请批次的数据访问类
** 版    本: 2.0.0.0
** 修改描述:
** 修 改 人:
** 修改日期:
******************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Text;
using Honestysoft.BaseObject;
using Honestysoft.Financing.Model;

namespace Honestysoft.Financing.DataAccess
{
	/// <summary>
    /// Bu_AuditBatch数据访问类
    /// </summary>
	public partial class Bu_AuditBatch_DAL : BaseDataAccess<Bu_AuditBatch>
    {		
		/// <summary>
        /// 构造函数，使用指定的连接字符串
        /// </summary>
        /// <param name="connectionStringName">连接字符串名称</param>
        public Bu_AuditBatch_DAL(string connectionStringName)
            : base(connectionStringName)
        {

        }

        public DataTable GetBatchs(int? batch,string status, int pageSize, int currentPageIndex, out int pageCount, out int recordCount)
        {
            DbParameter parameter = CreateParameter("BatchNo");
            parameter.DbType  = DbType.Int32;
            DbParameter pStatus = CreateParameter("BatchStatus");
            pStatus.DbType = DbType.String;
            string sql = string.Format(@"Select A.*,D.UserName CreatorName
                From Bu_AuditBatch A
                Inner Join Sys_User D On A.Creator=D.UserID
                Where 1=1 {0} {1}", batch.HasValue ? string.Format(" And A.BatchNo={0}", parameter.ParameterName) : "",
                                                    string.IsNullOrEmpty(status) ? "" : string.Format(" And A.BatchStatus={0}",pStatus.ParameterName));
            DbCommand command = GetDbCommand(sql);
            if (batch.HasValue)
            {
                parameter.Value = batch.Value;
                command.Parameters.Add(parameter);
            }
            if (!string.IsNullOrEmpty(status))
            {
                pStatus.Value = status;
                command.Parameters.Add(pStatus);
            }
            return ExecuteDataTable(command, "BatchNo Desc", pageSize, currentPageIndex, out pageCount, out recordCount);
        }

        public int GetNextBatchNo()
        {
            string sql = @"Select max(BatchNo) MAXNO From Bu_AuditBatch";
            object obj = ExecuteScalar(sql);
            if (obj == null || obj == DBNull.Value)
            {
                return 1;
            }
            return Convert.ToInt32(obj) + 1;
        }

        public string GetMaxBatchID()
        {
            string sql = @"Select ID From Bu_AuditBatch Where BatchNo=(Select Max(BatchNo) From Bu_AuditBatch)";
            object obj = ExecuteScalar(sql);
            if (obj == null || obj == DBNull.Value) { return ""; }
            return obj.ToString();
        }

        public int GetCount()
        {
            string sql = @"Select Count(ID) From Bu_AuditBatch";
            object obj = ExecuteScalar(sql);
            if (obj == null || obj == DBNull.Value)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }
        /// <summary>
        /// 得到已经完成的批次
        /// </summary>
        /// <returns></returns>
        public int GetCompleteCount()
        {
            string sql = @"Select Count(ID) From Bu_AuditBatch Where BatchStatus='"+((int)EnumItem.BatchStatus.Output).ToString()+"'";
            object obj = ExecuteScalar(sql);
            if (obj == null || obj == DBNull.Value)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 创建批次内部审核作业表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="userId"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int CreateBatchTable(string id,string userId, DbTransaction transaction)
        {
            DbParameter pID = CreateParameter("batchID");
            pID.DbType = DbType.String;
            pID.Value = id;
            DbParameter pUser = CreateParameter("UserID");
            pUser.DbType = DbType.String;
            pUser.Value = userId;
            DbCommand command = GetStoredProcCommand("Proc_CreateBatchAT");
            command.Parameters.Add(pID);
            command.Parameters.Add(pUser);
            return ExecuteNonQuery(command, transaction);
        }

        /// <summary>
        /// 删除批次回执包含的作业表
        /// </summary>
        /// <param name="batchID"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int DeleteBatchTable(string batchID, DbTransaction transaction)
        {
            DbParameter pID = CreateParameter("BatchID");
            pID.DbType = DbType.String;
            pID.Value = batchID;
            string sql = string.Format(@"Delete From Bu_AuditTable Where ID
                Not in
                (Select TableID From Bu_BatchRec Where BatchID={0})
                And BatchID={0}", pID.ParameterName);
            DbCommand command = GetDbCommand(sql);
            command.Parameters.Add(pID);
            return ExecuteNonQuery(command, transaction);
        }

        /// <summary>
        /// 计算批次内部审核作业表
        /// </summary>
        /// <param name="id"></param>
        /// <param name="transaction"></param>
        /// <returns></returns>
        public int ComputeBatchTable(string id, DbTransaction transaction)
        {
            DbParameter pID = CreateParameter("batchID");
            pID.DbType = DbType.String;
            pID.Value = id;
            DbCommand command = GetStoredProcCommand("Proc_ComputeBatch");
            command.Parameters.Add(pID);
            return ExecuteNonQuery(command, transaction);
        }

        public int CheckCanLockBatch(string batchID)
        {
            string sql = @"Select Count(ID) From Bu_AuditTable 
                Where BatchID='" + batchID + "' And ( BuStatus='" + ((int)EnumItem.AuditTableStatus.FAppr).ToString() + @"'
                Or  BuStatus='" + ((int)EnumItem.AuditTableStatus.NoRec).ToString() + @"'
                Or BuStatus='" + ((int)EnumItem.AuditTableStatus.SAppr).ToString() + @"')";
            object obj = ExecuteScalar(sql);
            return Convert.ToInt32(obj);
        }

        public int CheckCanOutPutBatch(string batchID)
        {
            string sql = @"Select Count(ID) From Bu_AuditTable 
                Where BatchID='" + batchID + "' And BuStatus<>'" + ((int)EnumItem.AuditTableStatus.Output).ToString() + @"' And BuStatus<>'" + ((int)EnumItem.AuditTableStatus.Lock).ToString()+"'";
            object obj = ExecuteScalar(sql);
            return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 得到发改委返回的批次
        /// </summary>
        /// <param name="from"></param>
        /// <param name="to"></param>
        /// <returns></returns>
        public DataTable GetBatchFGW(int from, int to)
        {
            string sql = string.Format(@"Select A.ID,A.BatchNo,Count(B.Counter) as Counter From Bu_AuditBatch A
                Left Join Bu_AuditTable_FGW B On A.ID=B.BatchID
                Where A.BatchNo between {0} and {1} 
                Group By A.ID,A.BatchNo
                order by A.batchNo desc", from.ToString(), to.ToString());
            return ExecuteDataTable(sql);
        }
    }
}
